package Android.Billarm;

import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorWrapper;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DB_SQLite {
	
	//database columns
	public static final String KEY_ROWID = "_id";
	//public static final String KEY_BILLARM = "billarm";
	//public static final String KEY_BILL_NAME = "bill_name";
	public static final String KEY_BILL_AMOUNT = "bill_amount";
	//The year,month,day,hour,minute that the billarm will go off represented in
    // the database as: TEXT as ISO8601 string ("YYYY-MM-DD HH:MM")
    public static final String KEY_BILLARM_DATE = "billarm_date";
    //stores icons as comma separated values
    public static final String KEY_ICONS = "billarm_icons";
    //active is a boolean value, stored as an integer 0 (false) and 1 (true).
    public static final String KEY_ACTIVE = "active";
    
    private static final String TAG = "DB_SQLite";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private static final String DATABASE_NAME = "billarmTest";
    private static final String DATABASE_TABLE = "billarms";
    private static final int DATABASE_VERSION = 2;
    
    //database create statement
    private static final String DATABASE_CREATE =
    	"create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
            + KEY_BILL_AMOUNT + " numeric, "
            + KEY_BILLARM_DATE + " text not null, "
            + KEY_ICONS + " text not null, "
            + KEY_ACTIVE + " integer not null);";
    
    private final Context mCtx;
    
    private static class DatabaseHelper extends SQLiteOpenHelper {    	
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d ("DB_SQLite.DatabaseHelper", "onCreate!");
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public DB_SQLite(Context ctx) {
        this.mCtx = ctx;
    }
    
    /**
     * Open the billarms database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public DB_SQLite open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public boolean isOpen() {
    	return mDb.isOpen();
    }
    
    public void close() {
        mDbHelper.close();
    }
    
    public void dropTable() {
    	mDb.execSQL("DROP TABLE IF EXISTS " + DATABASE_NAME + "." + DATABASE_TABLE);
    }
    
    /**
     * Inserts a new bill to the database. If the bill is successfully 
     * inserted return the new rowId for that bill, otherwise return
     * a -1 to indicate failure.
     * 
     * @param newBill the bill to be added
     * @return rowId or -1 if failed
     */
    public long insertBillarm(Bill newBill) {
    	//compare current date to updated date (0 for false--inactive, 1 for true--active)
    	//int billarmIsActive = newBill.getBillarmDate().after(new GregorianCalendar()) ? 1 : 0;
    	
    	ContentValues values = new ContentValues();
    	values.put(KEY_BILL_AMOUNT, newBill.getAmount());
    	values.put(KEY_BILLARM_DATE, newBill.getBillarmDate().getISODate());
    	values.put(KEY_ICONS, newBill.getIconsCommaSeparated());
    	values.put(KEY_ACTIVE, newBill.activep ? 1 : 0);
    	
        newBill.id = mDb.insert(DATABASE_TABLE, null, values);
        return newBill.id;
    }
    
    /**
     * Delete the billarm with the given rowId
     * 
     * @param rowId id of billarm to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteBillarm(long billId) {
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + billId, null) > 0;
    }
    
    /**
     * Return a Cursor positioned at the bill that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if bill could not be found/retrieved
     */
    public Cursor fetchBillCursor(long rowId) throws SQLException {
    	Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
            KEY_BILL_AMOUNT, KEY_BILLARM_DATE, KEY_ICONS, KEY_ACTIVE}, KEY_ROWID + "=" + rowId,
            null, null, null, null, null);
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    	}
    	return mCursor;
    }
    
    //fetch a bill from the database and return it as a Bill object
    public Bill fetchBill(long rowId) {
        Cursor c = fetchBillCursor(rowId);
        Bill bill = cursorToBill(c);
        c.close ();
        return bill;
    }
    
    /**
     * Return a Cursor over the list of all billarms in the database
     * 
     * @return Cursor over all billarms
     */
    public Cursor fetchAllBillarms() {
        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_BILL_AMOUNT,
        	KEY_BILLARM_DATE, KEY_ICONS, KEY_ACTIVE}, null, null, null, null, KEY_BILLARM_DATE);
    }
    
    //fetch all bills from database and return them as List<Bill>
    public List<Bill> fetchAllBillarmsList() {
    	List<Bill> billsList = new ArrayList<Bill> ();
    	CursorWrapper billsCursor = new CursorWrapper(fetchAllBillarms());
    	
    	if(billsCursor.getCount() > 0)
    		billsList.add(cursorToBill(billsCursor));
    	
    	while(billsCursor.moveToNext()) {
    		billsList.add(cursorToBill(billsCursor));
    	}
    	
    	return billsList;
    }
    
    /**
     * Return a Cursor over the list of active billarms in the database
     * 
     * @return Cursor over active billarms
     */
    public Cursor fetchActiveBillarms() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_BILL_AMOUNT,
    			KEY_BILLARM_DATE, KEY_ICONS, KEY_ACTIVE}, KEY_ACTIVE + " = 1",
                null, null, null, KEY_BILLARM_DATE);
    }
     
    
    /**
     * Return a Cursor over the list of inactive billarms in the database
     * 
     * @return Cursor over inActive billarms
     */
    public Cursor fetchInactiveBillarms() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_BILL_AMOUNT,
    			KEY_BILLARM_DATE, KEY_ICONS, KEY_ACTIVE}, KEY_ACTIVE + " = 0",
                null, null, null, KEY_BILLARM_DATE);
    }
    
    /**
     * Update the billarm using the details provided. The billarm to be updated is
     * specified using the rowId, and it is altered to use the Bill and Recurrence
     * values passed in
     * 
     * @param updatedBill the updated Bill
     * @return true if the billarm was successfully updated, false otherwise
     */
    public boolean updateBillarm(Bill updatedBill) {
    	//compare current date to updated date (0 for false--inactive, 1 for true--active)
    	//int billarmIsActive = updatedBill.getBillarmDate().after(new GregorianCalendar()) ? 1 : 0;
    	
    	ContentValues values = new ContentValues();
    	values.put(KEY_BILL_AMOUNT, updatedBill.getAmount());
    	values.put(KEY_BILLARM_DATE, updatedBill.getBillarmDate().getISODate());
    	values.put(KEY_ICONS, updatedBill.getIconsCommaSeparated());
    	values.put(KEY_ACTIVE, updatedBill.activep ? 1 : 0);
    	
        int ret = mDb.update(DATABASE_TABLE, values, KEY_ROWID + " = " + updatedBill.id, null);
        if (ret == 1)
           Log.d ("DB_SQLite", "update success!");
        else
            Log.d ("DB_SQLite", "what? " + ret + " rows affected on update!");
        return ret == 1;
    }
    
    //converts from cursor to bill object
    public static Bill cursorToBill(Cursor billCursor) {
        try
        {
            long id = billCursor.getLong(billCursor.getColumnIndexOrThrow(KEY_ROWID));
            long amount = billCursor.getLong(billCursor.getColumnIndexOrThrow(KEY_BILL_AMOUNT));
            DBISODate billarmDate = new DBISODate(billCursor.getString(billCursor.getColumnIndexOrThrow(KEY_BILLARM_DATE)));
            String iconsString = billCursor.getString(billCursor.getColumnIndexOrThrow(KEY_ICONS));
            boolean activep = (1 == billCursor.getInt(billCursor.getColumnIndexOrThrow(KEY_ACTIVE)));

            return new Bill(id, amount, billarmDate, iconsString, activep);
        }
        catch (Exception e)
        {
            return null;
        }
    }
}
